package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import model.ProductModel;

public class Products {
	
	public Products()
	{
	}
	
	/*public List<ProductModel> listProducts(String proCus)
	{
		List<ProductModel> list=new ArrayList<ProductModel>();
		Connection con=new Connect().getConnection();
		String sql="SELECT * FROM PRODUCTS WHERE ID LIKE '"
			+ proCus + "%' ORDER BY ID ASC";
		try{
			PreparedStatement state =con.prepareStatement(sql);
			ResultSet rs=state.executeQuery();
			while(rs.next())
			{
				list.add(new ProductModel(
						rs.getString("id"),
						rs.getString("name"),
						rs.getDouble("price"),
						rs.getInt("quantities"),
						rs.getString("type_id"),
						rs.getString("event_id"),
						rs.getString("image_url"),
						rs.getString("date_expire")));
			}
			con.close();
		}catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}*/
	
	public List<ProductModel> getProducts(String id, String name, String typeId, String eventId)
	{
		List<ProductModel> list=new ArrayList<ProductModel>();
		Connection con=new Connect().getConnection();
		String sql;
		if(id != null){
			sql = "SELECT * FROM PRODUCTS WHERE ID = '"
				+ id + "' ORDER BY ID ASC";
		}else if(name != null){
			sql = "SELECT * FROM PRODUCTS WHERE NAME LIKE '"
				+ name + "%' ORDER BY ID ASC";
		}else if(typeId != null){
			sql = "SELECT * FROM PRODUCTS WHERE TYPE_ID = '"
				+ typeId + "' ORDER BY ID ASC";
		}else if(eventId != null){
			sql = "SELECT * FROM PRODUCTS WHERE EVENT_ID = '"
				+ eventId + "' ORDER BY ID ASC";
		}else{
			sql = "SELECT * FROM PRODUCTS WHERE NAME LIKE '"
				+ name + "%' AND ID ='" 
				+ id + "' AND TYPE_ID ='"
				+ typeId + "' AND EVENT_ID ='" 
				+ eventId +"' ORDER BY ID ASC";
		}
		
		try{
			PreparedStatement state =con.prepareStatement(sql);
			ResultSet rs=state.executeQuery();
			while(rs.next())
			{
				list.add(new ProductModel(
						rs.getString("id"),
						rs.getString("name"),
						rs.getDouble("price"),
						rs.getInt("quantities"),
						rs.getString("type_id"),
						rs.getString("event_id"),
						rs.getString("image_url"),
						rs.getString("date_expire")));
			}
			con.close();
		}catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	
	public List<ProductModel> ListProducts() {
		List<ProductModel> list = new ArrayList<ProductModel>();
		Connection con = new Connect().getConnection();
		String sql = "SELECT * FROM PRODUCTS ORDER BY ID DESC";
		try {
			PreparedStatement state = con.prepareStatement(sql);
			ResultSet rs = state.executeQuery();
			while (rs.next()) {
				list.add(new ProductModel(
						rs.getString("id"),
						rs.getString("name"),
						rs.getDouble("price"),
						rs.getInt("quantities"),
						rs.getString("type_id"),
						rs.getString("event_id"),
						rs.getString("image_url"),
						rs.getString("date_expire")));
			}
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	public boolean UpdateProduct(ProductModel pro) {
		Connection con = new Connect().getConnection();
		String sql = "UPDATE PRODUCTS SET ID = '" + pro.getId()+ "', NAME = '"
				+ pro.getName() + "', PRICE ="
				+ pro.getQuantities() + ", QUANTITIES ="
				+ pro.getPrice()  + ", TYPE_ID = '" 
				+ pro.getType_id() + "', EVENT_ID = '" 
				+ pro.getEvent_id() + "', IMAGE_URL = '"
				+ pro.getImage_url()+ "', DATE_EXPIRE = '"
				+ pro.getDate_expire()+ "' WHERE ID  = '" + pro.getId()
				+ "'";
		try {
			PreparedStatement state = con.prepareStatement(sql);
			state.executeUpdate();
			con.close();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}
	
	public boolean SaveProductImage(String proId, String url) throws SQLException {
		Connection con = new Connect().getConnection();
		String sqlGet = "SELECT * FROM PRODUCTS WHERE ID = '"
						+ proId + "'";
		String sqlUpdate = "UPDATE PRODUCTS SET IMAGE_URL = '"
				+ url+ "' WHERE ID  = '" + proId + "'";
		try {
			PreparedStatement state = con.prepareStatement(sqlUpdate);
			state.executeUpdate();
			con.close();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			con.close();
		}
		return false;
	}
	
	public void DeleteProduct(String ProID) {
		Connection con = new Connect().getConnection();
		String sql = "DELETE FROM PRODUCTS WHERE ID = '" + ProID + "'";
		try {
			PreparedStatement state = con.prepareStatement(sql);
			state.executeUpdate();
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public boolean CreateProducts(ProductModel pro) {
		Connection con = new Connect().getConnection();
		String sql = "INSERT INTO PRODUCTS VALUES('" + pro.getId()
				+ "', '" + pro.getName() + "', " + pro.getPrice() + ","+pro.getQuantities()+ 
				",'"+ pro.getType_id()+"','"+pro.getEvent_id()
				+"','"+pro.getImage_url()+ "','" +pro.getDate_expire()+"')";
		try {
			PreparedStatement state = con.prepareStatement(sql);
			state.executeUpdate();
			con.close();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

	public boolean CheckExistProduct(String ProID) {
		Connection con = new Connect().getConnection();
		String sql = "SELECT ID FROM PRODUCTS WHERE ID = '" + ProID
				+ "'";
		try {
			PreparedStatement state = con.prepareStatement(sql);
			ResultSet rs = state.executeQuery();
			if (rs.next())
				return true;
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}
}